package org.emet.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.emet.entity.biz.Carriage;
import org.emet.jee.core.dao.Page;
import org.emet.jee.core.dao.arch.ArchDataSourceBaseDao;
import org.emet.service.dto.CarriageCriteria;
import org.emet.service.dto.CarriageStatisticsDisplayItem;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class CarriageDao extends ArchDataSourceBaseDao<Carriage, Long> {

	public Page<Carriage> search(Page<Carriage> page, CarriageCriteria transport) {
		Criteria criteria = getSession().createCriteria(Carriage.class);
		NonEmptyValueHashMap<String, Object> propertyNameValues = new NonEmptyValueHashMap<String, Object>();
		propertyNameValues.put("bizNo", transport.getBizNo());
		propertyNameValues.put("deceadName", transport.getDeceadName());
		propertyNameValues.put("dpyName", transport.getdPyName());
		propertyNameValues.put("contactName", transport.getdPyName());
		propertyNameValues.put("cPyName", transport.getcPyName());
		propertyNameValues.put("phone", transport.getPhone());
		propertyNameValues.put("bizUnit", transport.getBizUnit());
		if (transport.getDate() != null) {
			criteria.add(Restrictions.sqlRestriction("date(date) = '"
					+ new SimpleDateFormat("yyyy-MM-dd").format(transport.getDate()) + "'"));
		}
		if (StringUtils.isNotBlank(transport.getTime()) && !transport.getTime().equals("00:00")) {
			criteria.add(Restrictions.sqlRestriction("time(date) = '" + transport.getTime() + ":00'"));
		}
		String vehicles = transport.getVehicles();
		if (!StringUtils.isBlank(vehicles)) {
			criteria.createAlias("vehicles", "veh").add(Restrictions.eq("veh.lpn", vehicles));
		}
		String drivers = transport.getDrivers();
		if (!StringUtils.isBlank(drivers)) {
			criteria.createAlias("drivers", "dr").add(Restrictions.eq("dr.name", drivers));
		}
		String address = transport.getAddress();
		if (!StringUtils.isBlank(address)) {
			criteria.add(Restrictions.like("address", "%" + address + "%"));
		}
		criteria.add(Restrictions.allEq(propertyNameValues));
		page.setOrderBy("id");
		page.setOrder("desc");
		return findPage(page, criteria);
	}

	public List<CarriageStatisticsDisplayItem> searchByYear(int startYear, int endYear, boolean groupByDriver) {
		if (!groupByDriver)
			return getJdbcTemplate()
					.query("SELECT 1 AS DRIVER,YEAR(DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CARRIAGE WHERE YEAR(DATE)>=? AND YEAR(DATE) <= ? GROUP BY YEAR(DATE)",
							reportRowMap, startYear, endYear);
		else
			return getJdbcTemplate()
					.query("SELECT E.NAME AS DRIVER,YEAR(C.DATE) AS DATESTRING,COUNT(C.ID) AS COUNT FROM (SELECT ID,DATE FROM T_CARRIAGE WHERE YEAR(DATE)>=? AND YEAR(DATE) <= ?) AS C JOIN T_CARRIAGE_DRIVER AS TCD ON TCD.CARRIAGE_ID=C.ID JOIN T_EMPLOYEE AS E ON TCD.DRIVER_ID = E.ID GROUP BY TCD.DRIVER_ID,YEAR(C.DATE) ORDER BY E.NAME DESC",
							reportRowMap, startYear, endYear);
	}

	public List<CarriageStatisticsDisplayItem> searchByMon(int startYear, int startMon, int endYear, int endMon,
			boolean groupByDriver) {
		if (!groupByDriver)
			return getJdbcTemplate()
					.query("SELECT 1 AS DRIVER,CONCAT(YEAR(DATE),'-',MONTH(DATE)) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CARRIAGE WHERE (YEAR(DATE)>=?  AND MONTH(DATE) >= ?) AND (YEAR(DATE) <= ? AND MONTH(DATE) <= ?) GROUP BY YEAR(DATE) , MONTH(DATE)",
							reportRowMap, startYear, startMon, endYear, endMon);
		else
			return getJdbcTemplate()
					.query("  SELECT E.NAME AS DRIVER,CONCAT(YEAR(DATE),'-',MONTH(DATE)) AS DATESTRING,COUNT(C.ID) AS COUNT FROM (SELECT ID,DATE FROM T_CARRIAGE WHERE (YEAR(DATE)>=?  AND MONTH(DATE) >= ?) AND (YEAR(DATE) <= ? AND MONTH(DATE) <= ?)) AS C JOIN T_CARRIAGE_DRIVER AS TCD ON TCD.CARRIAGE_ID=C.ID JOIN T_EMPLOYEE AS E ON TCD.DRIVER_ID = E.ID GROUP BY TCD.DRIVER_ID,YEAR(C.DATE),MONTH(DATE) ORDER BY E.NAME DESC",
							reportRowMap, startYear, startMon, endYear, endMon);
	}

	public List<CarriageStatisticsDisplayItem> searchByDate(Date startDate, Date endDate, boolean groupByDriver) {
		if (!groupByDriver)
			return getJdbcTemplate()
					.query(" SELECT 1 AS DRIVER,DATE(DATE) AS DATESTRING,COUNT(ID) AS COUNT FROM T_CARRIAGE  WHERE DATE(DATE)>=DATE(?) AND DATE(DATE) <= DATE(?)  GROUP BY DATE(DATE)",
							reportRowMap, startDate, endDate);
		else
			return getJdbcTemplate()
					.query(" SELECT E.NAME AS DRIVER,DATE(C.DATE) AS DATESTRING,COUNT(C.ID) AS COUNT FROM (SELECT ID,DATE FROM T_CARRIAGE WHERE Date(DATE)>=? AND date(DATE) <= ? ) AS C JOIN T_CARRIAGE_DRIVER AS TCD ON TCD.CARRIAGE_ID=C.ID JOIN T_EMPLOYEE AS E ON TCD.DRIVER_ID = E.ID GROUP BY TCD.DRIVER_ID,DATE(C.DATE) ORDER BY E.NAME",
							reportRowMap, startDate, endDate);
	}

	private static final ParameterizedRowMapper<CarriageStatisticsDisplayItem> reportRowMap = new ParameterizedRowMapper<CarriageStatisticsDisplayItem>() {
		public CarriageStatisticsDisplayItem mapRow(ResultSet rs, int paramInt) throws SQLException {
			CarriageStatisticsDisplayItem c = new CarriageStatisticsDisplayItem();
			c.setCount(rs.getInt("COUNT"));
			c.setDateString(rs.getString("DATESTRING"));
			c.setDriverName(rs.getString("DRIVER"));
			return c;
		}
	};

	public Carriage findLastestByVeh(Long vehId) {
		Query query = getSession().createQuery("from Carriage where vehId = ? order by id desc");
		query.setMaxResults(1);
		query.setParameter(0, vehId);
		@SuppressWarnings("unchecked")
		Iterator<Carriage> it = query.iterate();
		if (it.hasNext())
			return it.next();
		return null;
	}

	public List<Carriage> findUnpaiedByBiz(String bizNo) {
		return find("from Carriage where paied = false and bizNo = ?", bizNo);
	}

	public void setAsPaied(Long chargeId) {
		Carriage carriage = get(chargeId);
		if (null != carriage) {
			carriage.setPaied(true);
		}
	}
}
